package com.sevennotes.classtable.data.database.daos

import androidx.paging.PagingSource
import androidx.room.*
import com.sevennotes.classtable.model.ClassRecord
import com.sevennotes.classtable.model.ClassRecordMonthStatis
import com.sevennotes.classtable.model.ClassRecordWithName
import kotlinx.coroutines.flow.Flow
import java.util.*

@Dao
interface ClassRecordDAO {
    @Insert
    fun insertClassRecord(classRecord: ClassRecord)

    @Update
    fun updateClassRecord(classRecord: ClassRecord)

    @Delete
    fun deleteClassRecord(classRecord: ClassRecord)

    @Query("DELETE FROM class_record")
    fun clear()

    @Query("SELECT * FROM class_record")
    fun getAllClassRecordForBack():List<ClassRecord>

    @Query(
        "SELECT class_record.*, student_items.name as student_name " +
            "FROM class_record LEFT JOIN student_items ON student_items.id = class_record.student_id " +
            "WHERE start_time >= :startTime AND start_time <= :toTime AND student_name LIKE '%' || :name || '%' " +
            "ORDER BY class_record.start_time DESC"
    )
    fun getAllClassRecordOld(
        startTime: Long,
        toTime: Long,
        name: String
    ): PagingSource<Int, ClassRecordWithName>


    @Query(
        "SELECT class_record.*, student_items.name as student_name " +
                "FROM class_record LEFT JOIN student_items ON student_items.id = class_record.student_id " +
                "ORDER BY class_record.start_time DESC"
    )
    fun getAllClassRecord(): PagingSource<Int, ClassRecordWithName>

    @Query("SELECT class_record.*, student_items.name as student_name " +
            "FROM class_record LEFT JOIN student_items ON student_items.id = class_record.student_id " +
            "WHERE strftime('%Y', class_record.start_time / 1000, 'unixepoch') IN (:years) " +
            "ORDER BY class_record.start_time DESC")
    fun getClassRecordsByYears(years: List<String>): List<ClassRecordWithName>

    @Query("""
        SELECT * FROM class_record 
        WHERE student_id = :studentId 
        ORDER BY start_time
    """)
    fun getAllClassRecordsByStudentId(studentId: Int): List<ClassRecord>

    @Query("SELECT DISTINCT strftime('%Y', start_time / 1000, 'unixepoch') FROM class_record ORDER BY start_time ASC")
    fun getAllStartYears(): List<String>

    @Query("SELECT strftime('%Y', start_time / 1000, 'unixepoch') AS year, " +
            "ltrim(strftime('%m', start_time / 1000, 'unixepoch')) AS month, " +
            "COUNT(*) AS total FROM class_record GROUP BY year, month")
    fun getClassRecordMonthStatistics(): Flow<List<ClassRecordMonthStatis>>

    @Query(
        "SELECT count(*) " +
            "FROM class_record LEFT JOIN student_items ON student_items.id = class_record.student_id " +
            "WHERE start_time >= :startTime AND start_time <= :toTime AND student_items.name LIKE '%' || :nameStr || '%'"
    )
    fun getCount(startTime: Long, toTime: Long, nameStr: String): Int

    @Query("DELETE FROM class_record WHERE student_id = :studentId")
    fun deleteAllByStudentId(studentId: Int)
}